package dataBase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Observable;

import core.Constants;

public class GeneralDB extends Observable {
	private final GeneralDB database = null;
	final String framework;
	final String driver;
	final String protocol;
	final String dbName;
	Connection connection;

	// list of Statements
	ArrayList statements = new ArrayList();

	// PreparedStatements
	PreparedStatement psInsert = null;
	PreparedStatement psUpdate = null;
	PreparedStatement psDelete = null;
	Statement statement = null;
	ResultSet resultSet = null;

	protected GeneralDB(String dbName) throws SQLException {
		this.framework = Constants.FRAMEWORK;
		this.driver = Constants.DRIVER;
		this.protocol = Constants.PROTOCOL;
		this.dbName = dbName;

		this.loadDriver();
		this.connect();
		this.createStatement();
	}

	public GeneralDB getInstance(String dbName) throws SQLException {
		if (this.database == null) {
			return new GeneralDB(dbName);
		} else {
			return this.database;
		}
	}

	private void loadDriver() {
		// load driver
		try {
			Class.forName(this.driver).newInstance();
			// System.out.println("Loaded the appropriate driver");
		} catch (ClassNotFoundException cnfe) {
			System.err.println("\nUnable to load the JDBC driver " + this.driver);
			System.err.println("Please check your CLASSPATH.");
			cnfe.printStackTrace(System.err);
		} catch (InstantiationException ie) {
			System.err.println("\nUnable to instantiate the JDBC driver " + this.driver);
			ie.printStackTrace(System.err);
		} catch (IllegalAccessException iae) {
			System.err.println("\nNot allowed to access the JDBC driver " + this.driver);
			iae.printStackTrace(System.err);
		}
	}

	private void connect() {

		try {
			this.connection = DriverManager.getConnection(this.protocol + this.dbName + ";create=true");
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private void createStatement() throws SQLException {
		/*
		 * Creating a statement object that we can use for running various SQL
		 * statements commands against the database.
		 */
		try {
			if (this.statement == null) {
				this.statement = this.connection.createStatement();
				this.statements.add(this.statement);
			}
		} catch (NullPointerException e) {
			e.printStackTrace();
		}

	}

	public void deleteTable(String tableName) throws SQLException {
		// delete the table
		this.statement.execute("drop table " + tableName);
		// System.out.println("Dropped table " + aRCHERTABLENAME);
	}

	public void shudDown() {
		try {
			// the shutdown=true attribute shuts down Derby
			DriverManager.getConnection("jdbc:derby:;shutdown=true");

			// To shut down a specific database only, but keep the
			// engine running (for example for connecting to other
			// databases), specify a database in the connection URL:
			// DriverManager.getConnection("jdbc:derby:" + dbName +
			// ";shutdown=true");
		} catch (SQLException se) {
			if (((se.getErrorCode() == 50000) && ("XJ015".equals(se.getSQLState())))) {
				// we got the expected exception
				// System.out.println("Derby shut down normally");
				// Note that for single database shutdown, the expected
				// SQL state is "08006", and the error code is 45000.
			} else {
				// if the error code or SQLState is different, we have
				// an unexpected exception (shutdown failed)
				System.err.println("Derby did not shut down normally");
				System.err.println(se);
			}
		}
	}

	public void releasOpenRessources() {
		// ResultSet
		try {
			if (this.resultSet != null) {
				this.resultSet.close();
				this.resultSet = null;
			}
		} catch (SQLException sqle) {
			System.err.println(sqle);
		}

		// Statements and PreparedStatements
		int i = 0;
		while (!this.statements.isEmpty()) {
			// PreparedStatement extend Statement
			Statement st = (Statement) this.statements.remove(i);
			try {
				if (st != null) {
					st.close();
					st = null;
				}
			} catch (SQLException sqle) {
				System.err.println(sqle);
			}
		}

		// Connection
		try {
			if (this.connection != null) {
				this.connection.close();
				this.connection = null;
			}
		} catch (SQLException sqle) {
			System.err.println(sqle);
		}

	}

}
